package com.gaofen.exceltoword.util;

import com.gaofen.exceltoword.entity.exceltoword.*;
import org.apache.poi.ss.usermodel.*;
import org.springframework.stereotype.Component;

import java.io.FileInputStream;
import java.io.File;
import java.io.FileNotFoundException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.List;

@Component
public class ExcelReaderWhshape {

    // 从指定的Excel文件路径读取数据，并返回一个SuzhouDto列表
    public List<Whshape> readExcelDataGD(String excelFilePath) {
        List<Whshape> whshapeDtosList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Whshape whshape = new Whshape();
                whshape.setFid(getCellValue(row.getCell(0)));
                whshape.setNumber(getCellValue(row.getCell(1)));
                whshape.setCou(getCellValue(row.getCell(2)));
                whshape.setTblx(getCellValue(row.getCell(3)));
                whshape.setBz(getCellValue(row.getCell(4)));
                whshape.setLon(getCellValue(row.getCell(5)));
                whshape.setLat(getCellValue(row.getCell(6)));
                whshape.setArea(getCellValue(row.getCell(7)));
                whshape.setYyjjbntmj(getCellValue(row.getCell(8)));
                whshape.setGdmj(getCellValue(row.getCell(9)));
                whshape.setFl(getCellValue(row.getCell(10)));
                whshape.setFn(getCellValue(row.getCell(11)));
                whshape.setJcsj(getCellValue(row.getCell(12)));
                whshape.setSfwy(getCellValue(row.getCell(13)));
                whshape.setHcsj(getCellValue(row.getCell(14)));
                whshape.setTown(getCellValue(row.getCell(15)));
                whshape.setBhlx(getCellValue(row.getCell(16)));

                whshapeDtosList.add(whshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return whshapeDtosList;
    }

    public List<Whshape> readExcelDataGDWJ(File file) throws FileNotFoundException {
        List<Whshape> whshapeDtosList = new ArrayList<>();
     //   try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
       try { FileInputStream fileInputStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fileInputStream);
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Whshape whshape = new Whshape();
                whshape.setFid(getCellValue(row.getCell(0)));
                whshape.setNumber(getCellValue(row.getCell(1)));
                whshape.setCou(getCellValue(row.getCell(2)));
                whshape.setTblx(getCellValue(row.getCell(3)));
                whshape.setBz(getCellValue(row.getCell(4)));
                whshape.setLon(getCellValue(row.getCell(5)));
                whshape.setLat(getCellValue(row.getCell(6)));
                whshape.setArea(getCellValue(row.getCell(7)));
                whshape.setYyjjbntmj(getCellValue(row.getCell(8)));
                whshape.setGdmj(getCellValue(row.getCell(9)));
                whshape.setFl(getCellValue(row.getCell(10)));
                whshape.setFn(getCellValue(row.getCell(11)));
                whshape.setJcsj(getCellValue(row.getCell(12)));
                whshape.setSfwy(getCellValue(row.getCell(13)));
                whshape.setHcsj(getCellValue(row.getCell(14)));
                whshape.setTown(getCellValue(row.getCell(15)));
                whshape.setBhlx(getCellValue(row.getCell(16)));

                whshapeDtosList.add(whshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return whshapeDtosList;
    }

    public List<Ldshape> readExcelDataLD(String excelFilePath) {
        List<Ldshape> ldshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Ldshape ldshape = new Ldshape();
                ldshape.setFid(getCellValue(row.getCell(0)));
                ldshape.setLon(getCellValue(row.getCell(1)));
                ldshape.setLat(getCellValue(row.getCell(2)));
                ldshape.setNumber(getCellValue(row.getCell(3)));
                ldshape.setCou(getCellValue(row.getCell(4)));
                ldshape.setJyr(getCellValue(row.getCell(5)));
                ldshape.setJcsj(getCellValue(row.getCell(6)));
                ldshape.setArea(getCellValue(row.getCell(7)));
                ldshape.setTblx(getCellValue(row.getCell(8)));
                ldshape.setTbbm(getCellValue(row.getCell(9)));
                ldshape.setTown(getCellValue(row.getCell(10)));
                ldshape.setMu(getCellValue(row.getCell(11)));

                ldshapeList.add(ldshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return ldshapeList;
    }

    public List<Ldshape> readExcelDataLDWJ(File file) {
        List<Ldshape> ldshapeList = new ArrayList<>();
        try (//FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             FileInputStream fileInputStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Ldshape ldshape = new Ldshape();
                ldshape.setFid(getCellValue(row.getCell(0)));
                ldshape.setLon(getCellValue(row.getCell(1)));
                ldshape.setLat(getCellValue(row.getCell(2)));
                ldshape.setNumber(getCellValue(row.getCell(3)));
                ldshape.setCou(getCellValue(row.getCell(4)));
                ldshape.setJyr(getCellValue(row.getCell(5)));
                ldshape.setJcsj(getCellValue(row.getCell(6)));
                ldshape.setArea(getCellValue(row.getCell(7)));
                ldshape.setTblx(getCellValue(row.getCell(8)));
                ldshape.setTbbm(getCellValue(row.getCell(9)));
                ldshape.setTown(getCellValue(row.getCell(10)));
                ldshape.setMu(getCellValue(row.getCell(11)));

                ldshapeList.add(ldshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return ldshapeList;
    }
    public List<Ksscshape> readExcelDataKSSC(String excelFilePath) {
        List<Ksscshape> ksscshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Ksscshape ksscshape = new Ksscshape();
                ksscshape.setFid(getCellValue(row.getCell(0)));
                ksscshape.setX(getCellValue(row.getCell(1)));
                ksscshape.setY(getCellValue(row.getCell(2)));
                ksscshape.setArea(getCellValue(row.getCell(3)));
                ksscshape.setJcsj(getCellValue(row.getCell(4)));
                ksscshape.setTblx(getCellValue(row.getCell(5)));
                ksscshape.setNumber(getCellValue(row.getCell(6)));
                ksscshape.setCou(getCellValue(row.getCell(7)));
                ksscshape.setTown(getCellValue(row.getCell(8)));
                ksscshape.setKsmc(getCellValue(row.getCell(9)));

                ksscshapeList.add(ksscshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return ksscshapeList;
    }

    public List<Ksscshape> readExcelDataKSSCWJ(File file) {
        List<Ksscshape> ksscshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Ksscshape ksscshape = new Ksscshape();
                ksscshape.setFid(getCellValue(row.getCell(0)));
                ksscshape.setX(getCellValue(row.getCell(1)));
                ksscshape.setY(getCellValue(row.getCell(2)));
                ksscshape.setArea(getCellValue(row.getCell(3)));
                ksscshape.setJcsj(getCellValue(row.getCell(4)));
                ksscshape.setTblx(getCellValue(row.getCell(5)));
                ksscshape.setNumber(getCellValue(row.getCell(6)));
                ksscshape.setCou(getCellValue(row.getCell(7)));
                ksscshape.setTown(getCellValue(row.getCell(8)));
                ksscshape.setKsmc(getCellValue(row.getCell(9)));

                ksscshapeList.add(ksscshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return ksscshapeList;
    }

    public List<Kstcshape> readExcelDataKSTC(String excelFilePath) {
        List<Kstcshape> kstcshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Kstcshape kstcshape = new Kstcshape();
                kstcshape.setFid(getCellValue(row.getCell(0)));
                kstcshape.setX(getCellValue(row.getCell(1)));
                kstcshape.setY(getCellValue(row.getCell(2)));
                kstcshape.setArea(getCellValue(row.getCell(3)));
                kstcshape.setJcsj(getCellValue(row.getCell(4)));
                kstcshape.setTblx(getCellValue(row.getCell(5)));
                kstcshape.setNumber(getCellValue(row.getCell(6)));
                kstcshape.setCou(getCellValue(row.getCell(7)));
                kstcshape.setTown(getCellValue(row.getCell(8)));
                kstcshape.setKsmc(getCellValue(row.getCell(9)));

                kstcshapeList.add(kstcshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return kstcshapeList;
    }

    public List<Kstcshape> readExcelDataKSTCWJ(File file) {
        List<Kstcshape> kstcshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Kstcshape kstcshape = new Kstcshape();
                kstcshape.setFid(getCellValue(row.getCell(0)));
                kstcshape.setX(getCellValue(row.getCell(1)));
                kstcshape.setY(getCellValue(row.getCell(2)));
                kstcshape.setArea(getCellValue(row.getCell(3)));
                kstcshape.setJcsj(getCellValue(row.getCell(4)));
                kstcshape.setTblx(getCellValue(row.getCell(5)));
                kstcshape.setNumber(getCellValue(row.getCell(6)));
                kstcshape.setCou(getCellValue(row.getCell(7)));
                kstcshape.setTown(getCellValue(row.getCell(8)));
                kstcshape.setKsmc(getCellValue(row.getCell(9)));

                kstcshapeList.add(kstcshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return kstcshapeList;
    }
    public List<Ksyzlshape> readExcelDataKSYZL(String excelFilePath) {
        List<Ksyzlshape> KsyzlshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Ksyzlshape ksyzlshape = new Ksyzlshape();
                ksyzlshape.setFid(getCellValue(row.getCell(0)));
                ksyzlshape.setX(getCellValue(row.getCell(1)));
                ksyzlshape.setY(getCellValue(row.getCell(2)));
                ksyzlshape.setArea(getCellValue(row.getCell(3)));
                ksyzlshape.setJcsj(getCellValue(row.getCell(4)));
                ksyzlshape.setNumber(getCellValue(row.getCell(5)));
                ksyzlshape.setCou(getCellValue(row.getCell(6)));
                ksyzlshape.setTown(getCellValue(row.getCell(7)));
                ksyzlshape.setXzshlx(getCellValue(row.getCell(8)));
                ksyzlshape.setXmmc(getCellValue(row.getCell(9)));

                KsyzlshapeList.add(ksyzlshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return KsyzlshapeList;
    }

    public List<Ksyzlshape> readExcelDataKSYZLWJ(File file) {
        List<Ksyzlshape> KsyzlshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Ksyzlshape ksyzlshape = new Ksyzlshape();
                ksyzlshape.setFid(getCellValue(row.getCell(0)));
                ksyzlshape.setX(getCellValue(row.getCell(1)));
                ksyzlshape.setY(getCellValue(row.getCell(2)));
                ksyzlshape.setArea(getCellValue(row.getCell(3)));
                ksyzlshape.setJcsj(getCellValue(row.getCell(4)));
                ksyzlshape.setNumber(getCellValue(row.getCell(5)));
                ksyzlshape.setCou(getCellValue(row.getCell(6)));
                ksyzlshape.setTown(getCellValue(row.getCell(7)));
                ksyzlshape.setXzshlx(getCellValue(row.getCell(8)));
                ksyzlshape.setXmmc(getCellValue(row.getCell(9)));

                KsyzlshapeList.add(ksyzlshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return KsyzlshapeList;
    }

    public List<Zrbhdshape> readExcelDataZRBHD(String excelFilePath) {
        List<Zrbhdshape> zrbhdshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Zrbhdshape zrbhdshape = new Zrbhdshape();
                zrbhdshape.setFid(getCellValue(row.getCell(0)));
                zrbhdshape.setNumber(getCellValue(row.getCell(1)));
                zrbhdshape.setLon(getCellValue(row.getCell(2)));
                zrbhdshape.setLat(getCellValue(row.getCell(3)));
                zrbhdshape.setArea(getCellValue(row.getCell(4)));
                zrbhdshape.setTblx(getCellValue(row.getCell(5)));
                zrbhdshape.setGnq(getCellValue(row.getCell(6)));
                zrbhdshape.setAddress(getCellValue(row.getCell(7)));
                zrbhdshape.setJcsj(getCellValue(row.getCell(8)));
                zrbhdshape.setTbbm(getCellValue(row.getCell(9)));
                zrbhdshape.setBhqk(getCellValue(row.getCell(10)));
                zrbhdshape.setZhenming(getCellValue(row.getCell(11)));
                zrbhdshape.setXianming(getCellValue(row.getCell(12)));
                zrbhdshape.setBhdmc(getCellValue(row.getCell(13)));

                zrbhdshapeList.add(zrbhdshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return zrbhdshapeList;
    }

    public List<Zrbhdshape> readExcelDataZRBHDWJ(File file) {
        List<Zrbhdshape> zrbhdshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Zrbhdshape zrbhdshape = new Zrbhdshape();
                zrbhdshape.setFid(getCellValue(row.getCell(0)));
                zrbhdshape.setNumber(getCellValue(row.getCell(1)));
                zrbhdshape.setLon(getCellValue(row.getCell(2)));
                zrbhdshape.setLat(getCellValue(row.getCell(3)));
                zrbhdshape.setArea(getCellValue(row.getCell(4)));
                zrbhdshape.setTblx(getCellValue(row.getCell(5)));
                zrbhdshape.setGnq(getCellValue(row.getCell(6)));
                zrbhdshape.setAddress(getCellValue(row.getCell(7)));
                zrbhdshape.setJcsj(getCellValue(row.getCell(8)));
                zrbhdshape.setTbbm(getCellValue(row.getCell(9)));
                zrbhdshape.setBhqk(getCellValue(row.getCell(10)));
                zrbhdshape.setZhenming(getCellValue(row.getCell(11)));
                zrbhdshape.setXianming(getCellValue(row.getCell(12)));
                zrbhdshape.setBhdmc(getCellValue(row.getCell(13)));

                zrbhdshapeList.add(zrbhdshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return zrbhdshapeList;
    }

    public List<Wgjyshape> readExcelDataWGJY(String excelFilePath) {
        List<Wgjyshape> wgjyshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(new File(excelFilePath));
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Wgjyshape wgjyshape = new Wgjyshape();
                wgjyshape.setFid(getCellValue(row.getCell(0)));
                wgjyshape.setNumber(getCellValue(row.getCell(1)));
                wgjyshape.setCou(getCellValue(row.getCell(2)));
                wgjyshape.setTblx(getCellValue(row.getCell(3)));
                wgjyshape.setBz(getCellValue(row.getCell(4)));
                wgjyshape.setLon(getCellValue(row.getCell(5)));
                wgjyshape.setLat(getCellValue(row.getCell(6)));
                wgjyshape.setArea(getCellValue(row.getCell(7)));
                wgjyshape.setJcsj(getCellValue(row.getCell(8)));
                wgjyshape.setYnmj(getCellValue(row.getCell(9)));
                wgjyshape.setTown(getCellValue(row.getCell(10)));
                wgjyshape.setGdmj(getCellValue(row.getCell(11)));

                wgjyshapeList.add(wgjyshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return wgjyshapeList;
    }

    public List<Wgjyshape> readExcelDataWGJYWJ(File file) {
        List<Wgjyshape> wgjyshapeList = new ArrayList<>();
        try (FileInputStream fileInputStream = new FileInputStream(file);
             Workbook workbook = WorkbookFactory.create(fileInputStream)) {
            Sheet sheet = workbook.getSheetAt(0);
            boolean isFirstRow = true;

            for (Row row : sheet) {
                if (isFirstRow) {
                    isFirstRow = false;
                    continue; // 跳过第一行（标题行）
                }
                Wgjyshape wgjyshape = new Wgjyshape();
                wgjyshape.setFid(getCellValue(row.getCell(0)));
                wgjyshape.setNumber(getCellValue(row.getCell(1)));
                wgjyshape.setCou(getCellValue(row.getCell(2)));
                wgjyshape.setTblx(getCellValue(row.getCell(3)));
                wgjyshape.setBz(getCellValue(row.getCell(4)));
                wgjyshape.setLon(getCellValue(row.getCell(5)));
                wgjyshape.setLat(getCellValue(row.getCell(6)));
                wgjyshape.setArea(getCellValue(row.getCell(7)));
                wgjyshape.setJcsj(getCellValue(row.getCell(8)));
                wgjyshape.setYnmj(getCellValue(row.getCell(9)));
                wgjyshape.setTown(getCellValue(row.getCell(10)));
                wgjyshape.setGdmj(getCellValue(row.getCell(11)));

                wgjyshapeList.add(wgjyshape);
            }
        } catch (Exception e) {
            // 根据自己需求，实现自定义异常处理机制
            e.printStackTrace();
        }
        return wgjyshapeList;
    }

    // 根据单元格类型获取单元格的值
    public String getCellValue(Cell cell) {
        if (cell == null) {
            return "";
        }
        switch (cell.getCellType()) {
            case STRING:
                return cell.getStringCellValue();
            case NUMERIC:
                if (DateUtil.isCellDateFormatted(cell)) {
                    // 可选：将日期转换为格式化的日期字符串
                    return cell.getDateCellValue().toString();
                } else {
                    return Double.toString(cell.getNumericCellValue());
                }
            case BOOLEAN:
                return Boolean.toString(cell.getBooleanCellValue());
            case FORMULA:
                return cell.getCellFormula();
            default:
                return "";  // 对于空白和其他类型
        }
    }

    // todo 如果想将所有类型的单元格统一转换为字符串类型，注释掉上面的getCellValue方法，调用该方法即可
    //private String getCellValue(Cell cell) {
    //    if (cell == null) {
    //        return "";
    //    }
    //    cell.setCellType(CellType.STRING);
    //    return cell.getStringCellValue();
    //}
}